Option Explicit

' ======================================================================
' Const
' ======================================================================
Private Const CONFIG_START_ROW_NO As Integer = 1

' 1. 数据生成配置 sheet 的默认名称
Private Const SHEET_NAME_SQL_DATA_CONFIG As String = "SQLDataConfig"

' 2. config 部分的配置常量
' 2.1 config 部分的标题内容
Private Const SECTION_CONFIG_TITLE As String = "config"

' 2.2 config 部分内配置表格的表头
Private Const SECTION_CONFIG_TABLE_TITLE_KEY As String = "ConfigKey"
Private Const SECTION_CONFIG_TABLE_TITLE_VAL As String = "ConfigValue"

' 2.3 config 部分内配置表格的默认 key + value
Private Const CONFIG_KEY__PK_MARK As String = "pkMark"
Private Const CONFIG_VAL__PK_MARK As String = "pk"

Private Const CONFIG_KEY__TABLE_ID_INDEX As String = "tableIdIndex"
Private Const CONFIG_VAL__TABLE_ID_INDEX As Integer = 1

Private Const CONFIG_KEY__LOGIC_NAME_INDEX As String = "logicNameIndex"
Private Const CONFIG_VAL__LOGIC_NAME_INDEX As Integer = 2

Private Const CONFIG_KEY__PHYSICAL_NAME_INDEX As String = "physicalNameIndex"
Private Const CONFIG_VAL__PHYSICAL_NAME_INDEX As Integer = 3

Private Const CONFIG_KEY__COLUMN_TYPE_INDEX As String = "columnTypeIndex"
Private Const CONFIG_VAL__COLUMN_TYPE_INDEX As Integer = 4

Private Const CONFIG_KEY__COLUMN_SIZE_INDEX As String = "columnSizeIndex"
Private Const CONFIG_VAL__COLUMN_SIZE_INDEX As Integer = 5

Private Const CONFIG_KEY__IS_COLUMN_PK_INDEX As String = "isColumnPkIndex"
Private Const CONFIG_VAL__IS_COLUMN_PK_INDEX As Integer = 6

Private Const CONFIG_KEY__TAG_COLUMN As String = "tagColumn"
Private Const CONFIG_VAL__TAG_COLUMN As String = "b"

Private Const CONFIG_KEY__TABLE_DEFINE_START_COLUMN As String = "tableDefineStartColumn"
Private Const CONFIG_VAL__TABLE_DEFINE_START_COLUMN As String = "c"

Private Const CONFIG_KEY__SQL_PARAM_TAG_LEFT As String = "SQLParamTagLeft"
Private Const CONFIG_VAL__SQL_PARAM_TAG_LEFT As String = "#{"

Private Const CONFIG_KEY__SQL_PARAM_TAG_RIGHT As String = "SQLParamTagRight"
Private Const CONFIG_VAL__SQL_PARAM_TAG_RIGHT As String = "}"

'       通过这个属性来记录上一次操作新生成的，或者更新的 sheet 名
Private Const CONFIG_KEY__SQL_PARAM_LAST_CREATED_UPDATED_SHEET_NAME As String = "lastCreatedOrUpdatedSheetName"

' 3. dataStrategy 部分的配置常量
' 3.1 dataStrategy 部分的标题内容
Private Const SECTION_DATA_STRATEGY_TITLE As String = "dataStrategy"

' 3.2 dataStrategy 部分内配置表格的表头
Private Const DATA_STRATEGY_TABLE_TITLE_KEY As String = "ColumnName"
Private Const DATA_STRATEGY_TABLE_TITLE_VAL As String = "strategy"

' 4. SQL 部分的配置常量
' 4.1 SQL 部分的标题内容
Private Const SECTION_SQL_TITLE As String = "SQL"

' 5. SQLParameter 部分的配置常量
' 5.1 SQLParameter 部分的标题内容
Private Const SECTION_SQL_PARAMETER_TITLE As String = "SQLParameter"

' 5.2 SQLParameter 部分内配置表格的表头
Private Const SQL_PARAMETER_TABLE_TITLE_KEY As String = "ParameterKey"
Private Const SQL_PARAMETER_TABLE_TITLE_VAL As String = "ParameterValue"
Private Const SQL_PARAMETER_TABLE_TITLE_TYPE As String = "ParameterType"

' 6. tableDefine 部分的配置常量
' 6.1 tableDefine 部分的标题内容
Private Const SECTION_TABLE_DEFINE_TITLE As String = "tableDefine"

' 99. other
' 各配置表格的 key 所在的列
Private Const SECTION_TABLE_KEY_COLUMN_STR As String = "B"
Private Const SECTION_TABLE_VALUE_COLUMN_STR As String = "C"

' ======================================================================
' Field
' ======================================================================
Private configSh As ConfigSheet

' ======================================================================
' Constructor
' ======================================================================
Private Sub Class_Initialize()
End Sub

Private Sub Class_Terminate()
    Set configSh = Nothing
End Sub

' ======================================================================
' 初始化
' ======================================================================
' ==================================================
' 从头开始创建 sheet
' ==================================================
' 如果指定的 sheet 名称不存在，则从 0 开始创建一个配置 sheet
' 如果指定的 sheet 名称已经存在，则清空指定的 sheet，然后重新创建配置 sheet
' @optional-param book 用来创建 sheet 的 book
'                      如果没有传这个参数，默认使用 ActiveWorkbook
' @optional-param targetSheetName sheet 名称
'                                如果没有设置，将按照默认规则生成一个 sheet 名称，然后创建一个新的配置sheet
'                                默认的 sheet 名生成规则
'                                    默认使用名称 `SQLDataConfig`
'                                    如果 `SQLDataConfig` 已经存在了，则使用名称 `SQLDataConfig2`；如果 `SQLDataConfig2` 也存在，则使用名称 `SQLDataConfig3`，以此类推
'                                如果设置了 sheet 名称
'                                    如果在 book 中存在指定的 sheet 名称，将会从 book 中获取这个 sheet 对象，然后清空 sheet，并重新写入配置 sheet 的内容
'                                    如果在 book 中不存在指定的 sheet 名称，则创建一个同名的 sheet，并写入配置 sheet 的内容
Public Sub InitCreateBySheetName(Optional ByRef book As Workbook = Nothing, Optional ByRef targetSheetName As String)
    ' 1. 确认 book
    Dim realBook As Workbook
    If book is Nothing Then
        ' 如果没有传这个参数，默认使用 ActiveWorkbook
        Set realBook = ActiveWorkbook
    Else
        Set realBook = book
    End If

    ' 2. 确认 sheet 名
    Dim realSheetName As String
    If targetSheetName = "" Then
        ' 2.1 如果没有设置 sheet 名
        '     按照默认规则生成一个 sheet 名称
        realSheetName = createConfigSheetName(realBook)
    Else
        realSheetName = targetSheetName
    End If

    ' 3. 创建 sheet 读写对象
    Set configSh = New ConfigSheet
    configSh.InitBySheetName realBook, realSheetName

    ' 4. 清空 sheet，并将单元格设置为字符串
    configSh.clearAllToTextSheet

    ' 5. 写入配置 sheet 的基础内容
    writeBaseConfigSheet
End Sub

' 将一个指定的 sheet 对象包装成配置 sheet 对象
' @param targetSheet 作为配置 sheet 的 sheet 对象
Private Sub InitCreateBySheet(ByRef targetSheet As Worksheet)
    ' 1. 创建 sheet 读写对象
    '    参数的检查全部交给 ConfigSheet
    Set configSh = New ConfigSheet
    configSh.InitBySheet targetSheet

    ' 2. 清空 sheet，并将单元格设置为字符串
    configSh.clearAllToTextSheet

    ' 3. 写入配置 sheet 的基础内容
    writeBaseConfigSheet
End Sub

' 获取指定的配置 sheet 对象，如果这个 sheet 不存在，则进行创建
' @param targetSheetName 配置 sheet 的名字
' @optional-param book sheet 所在的 workbook
'                      如果没有传这个参数，默认使用 `ActiveWorkbook`
Public Sub InitBySheetName(ByRef targetSheetName As String, Optional ByRef book As Workbook)
    ' 1. 确认 book
    Dim realBook As Workbook
    If book is Nothing Then
        ' 如果没有传这个参数，默认使用 ActiveWorkbook
        Set realBook = ActiveWorkbook
    Else
        Set realBook = book
    End If

    ' 2. 创建 sheet 读写对象
    '    参数的检查全部交给 ConfigSheet
    '    这里不需要创建不存在的 sheet
    Set configSh = New ConfigSheet
    configSh.InitBySheetName book, targetSheetName, false
End Sub

' 将一个指定的 sheet 对象包装成配置 sheet 对象
' @param targetSheet 作为配置 sheet 的 sheet 对象
Public Sub InitBySheet(ByRef targetSheet As Worksheet)
    ' 1. 创建 sheet 读写对象
    '    参数的检查全部交给 ConfigSheet
    Set configSh = New ConfigSheet
    configSh.InitBySheet targetSheet
End Sub

' ==================================================
' 初始化一个工作 sheet
' ==================================================
' 按照默认规则创建一个配置 sheet 名称
'
' 默认的 sheet 名生成规则
'     默认使用名称 `SQLDataConfig`
'     如果 `SQLDataConfig` 已经存在了，则使用名称 `SQLDataConfig2`
'     如果 `SQLDataConfig2` 也存在，则使用名称 `SQLDataConfig3`，以此类推
'
' @param book 用来搜索 sheet 名称的 book，已防止生成重名的 sheet 名
'             这里不对 book 对象做空检查，需要调用者手动检查
' @return sheet 名称
Private Function createConfigSheetName(ByRef book As Workbook) As String
    createConfigSheetName = UtilsWorksheet.getUsableSheetName(book, SHEET_NAME_SQL_DATA_CONFIG)
End Function

' 写入配置 sheet 的基础内容
' 当前方法只有在 sheet 读写对象 configSh 初始化完成后才能被调用，否则会抛出异常
Private Sub writeBaseConfigSheet()
    Dim df As DataFrame
    Dim workRowNo As Long

    With configSh
        workRowNo = CONFIG_START_ROW_NO

        ' ==================================================
        ' 1 config
        ' ==================================================
        ' 1. 写入 config 的标题
        .writeSectionName workRowNo, SECTION_CONFIG_TITLE, rowBgColorNo:= UtilsContentFormat.COLOR_ORANGE
        workRowNo = workRowNo + 1

        ' 2. 设置 config 内的表格
        Set df = New DataFrame
        df.Init colNames:= Array(SECTION_CONFIG_TABLE_TITLE_KEY, SECTION_CONFIG_TABLE_TITLE_VAL)
        df.IDataFrame_appendRowByColDatas CONFIG_KEY__PK_MARK, CONFIG_VAL__PK_MARK
        df.IDataFrame_appendRowByColDatas CONFIG_KEY__TABLE_ID_INDEX, CONFIG_VAL__TABLE_ID_INDEX
        df.IDataFrame_appendRowByColDatas CONFIG_KEY__LOGIC_NAME_INDEX, CONFIG_VAL__LOGIC_NAME_INDEX
        df.IDataFrame_appendRowByColDatas CONFIG_KEY__PHYSICAL_NAME_INDEX, CONFIG_VAL__PHYSICAL_NAME_INDEX
        df.IDataFrame_appendRowByColDatas CONFIG_KEY__COLUMN_TYPE_INDEX, CONFIG_VAL__COLUMN_TYPE_INDEX
        df.IDataFrame_appendRowByColDatas CONFIG_KEY__COLUMN_SIZE_INDEX, CONFIG_VAL__COLUMN_SIZE_INDEX
        df.IDataFrame_appendRowByColDatas CONFIG_KEY__IS_COLUMN_PK_INDEX, CONFIG_VAL__IS_COLUMN_PK_INDEX
        df.IDataFrame_appendRowByColDatas CONFIG_KEY__TAG_COLUMN, CONFIG_VAL__TAG_COLUMN
        df.IDataFrame_appendRowByColDatas CONFIG_KEY__TABLE_DEFINE_START_COLUMN, CONFIG_VAL__TABLE_DEFINE_START_COLUMN
        df.IDataFrame_appendRowByColDatas CONFIG_KEY__SQL_PARAM_TAG_LEFT, CONFIG_VAL__SQL_PARAM_TAG_LEFT
        df.IDataFrame_appendRowByColDatas CONFIG_KEY__SQL_PARAM_TAG_RIGHT, CONFIG_VAL__SQL_PARAM_TAG_RIGHT

        ' 3. 输出表格
        .appendDataFrame workRowNo, df, needBorder:=True, needWriteTitle:=True, titleBgColorNo:=UtilsContentFormat.COLOR_GREEN

        ' 4. 重设行号: 向下移动表格行数 + 表头行数(1行)
        workRowNo = workRowNo + df.IDataFrame_Size + 1

        ' ==================================================
        ' 2. dataStrategy
        ' ==================================================
        ' 1. 向下额外增加 2 个空行
        workRowNo = workRowNo + 2

        ' 2. 写入标题
        .writeSectionName workRowNo, SECTION_DATA_STRATEGY_TITLE, rowBgColorNo:= UtilsContentFormat.COLOR_ORANGE
        workRowNo = workRowNo + 1

        ' 3.2.2 写入一个空表格
        .appendEmptyTable workRowNo, _
            Array( _
                DATA_STRATEGY_TABLE_TITLE_KEY, _
                DATA_STRATEGY_TABLE_TITLE_VAL _
            ), _
            5, titleBgColorNo:= UtilsContentFormat.COLOR_GREEN

        ' ==================================================
        ' 3. SQL
        ' ==================================================
        ' 1. 在这里直接合并需要向下移动的行数
        ' workRowNo = workRowNo + 6 ' 向下移动 6 行表格的行数 (1标题 + 5行)
        ' workRowNo = workRowNo + 2 ' 向下移动 2 行，其中两行作为表格和下一节的间隔，第三行作为下一节的标题
        workRowNo = workRowNo + 8

        ' 2. 写入标题
        .writeSectionName workRowNo, SECTION_SQL_TITLE, rowBgColorNo:= UtilsContentFormat.COLOR_ORANGE
        workRowNo = workRowNo + 10

        ' ==================================================
        ' 4. SQLParameter
        ' ==================================================
        ' 1. 写入标题
        .writeSectionName workRowNo, SECTION_SQL_PARAMETER_TITLE, rowBgColorNo:= UtilsContentFormat.COLOR_ORANGE
        workRowNo = workRowNo + 1

        ' 2. 写入一个空表格
        .appendEmptyTable workRowNo, _
            Array( _
                SQL_PARAMETER_TABLE_TITLE_KEY, _
                SQL_PARAMETER_TABLE_TITLE_VAL, _
                SQL_PARAMETER_TABLE_TITLE_TYPE _
            ), _
            5, titleBgColorNo:= UtilsContentFormat.COLOR_GREEN

        ' ==================================================
        ' 5. tableDefine
        ' ==================================================
        ' 1. 在这里直接合并需要向下移动的行数
        ' workRowNo = workRowNo + 6 ' 向下移动 6 行表格的行数 (1标题 + 5行)
        ' workRowNo = workRowNo + 2 ' 向下移动 2 行，其中两行作为表格和下一节的间隔，第三行作为下一节的标题
        workRowNo = workRowNo + 8

        ' 2. 写入标题
        .writeSectionName workRowNo, SECTION_TABLE_DEFINE_TITLE, rowBgColorNo:= UtilsContentFormat.COLOR_ORANGE

        ' ==================================================
        ' 6. sheet按照写入内容宽度自适应
        ' ==================================================
        .colAutoFit
    End With
End Sub

' ======================================================================
' Property
' ======================================================================
' ==================================================
' Property--config
' ==================================================
' 获取 config 部分使用的区域的起始行号和结束行号
Public Property Get SectionConfigRangeRowNo() As PrimitivePair
    Set SectionConfigRangeRowNo = configSh.getSectionUsageRangeRowNo(SECTION_CONFIG_TITLE)
End Property

' 获取 config 部分的起始行号
Public Property Get SectionConfigStartRowNo() As Long
    SectionConfigStartRowNo = configSh.getSectionUsageStartRowNo(SECTION_CONFIG_TITLE)
End Property

' 获取 config 部分的结束行号
Public Property Get SectionConfigEndRowNo() As Long
    SectionConfigEndRowNo = configSh.getSectionUsageLastRowNo(SECTION_CONFIG_TITLE)
End Property

' 获取 config 下的属性: pkMark
Public Property Get Config_pkMark() As String
    Config_pkMark = configSh.getValueFromSectionKVTable(SECTION_CONFIG_TITLE, CONFIG_KEY__PK_MARK, defaultVal:=CONFIG_VAL__PK_MARK)
End Property

' 获取 config 下的属性: tableIdIndex
Public Property Get Config_tableIdIndex() As Long
    Config_tableIdIndex = configSh.getNumberValueFromSectionKVTable(SECTION_CONFIG_TITLE, CONFIG_KEY__TABLE_ID_INDEX, defaultVal:=CONFIG_VAL__TABLE_ID_INDEX)
End Property

' 获取 config 下的属性: logicNameIndex
Public Property Get Config_logicNameIndex() As Long
    Config_logicNameIndex = configSh.getNumberValueFromSectionKVTable(SECTION_CONFIG_TITLE, CONFIG_KEY__LOGIC_NAME_INDEX, defaultVal:=CONFIG_VAL__LOGIC_NAME_INDEX)
End Property

' 获取 config 下的属性: physicalNameIndex
Public Property Get Config_physicalNameIndex() As Long
    Config_physicalNameIndex = configSh.getNumberValueFromSectionKVTable(SECTION_CONFIG_TITLE, CONFIG_KEY__PHYSICAL_NAME_INDEX, defaultVal:=CONFIG_VAL__PHYSICAL_NAME_INDEX)
End Property

' 获取 config 下的属性: columnTypeIndex
Public Property Get Config_columnTypeIndex() As Long
    Config_columnTypeIndex = configSh.getNumberValueFromSectionKVTable(SECTION_CONFIG_TITLE, CONFIG_KEY__COLUMN_TYPE_INDEX, defaultVal:=CONFIG_VAL__COLUMN_TYPE_INDEX)
End Property

' 获取 config 下的属性: columnSizeIndex
Public Property Get Config_columnSizeIndex() As Long
    Config_columnSizeIndex = configSh.getNumberValueFromSectionKVTable(SECTION_CONFIG_TITLE, CONFIG_KEY__COLUMN_SIZE_INDEX, defaultVal:=CONFIG_VAL__COLUMN_SIZE_INDEX)
End Property

' 获取 config 下的属性: isColumnPkIndex
Public Property Get Config_isColumnPkIndex() As Long
    Config_isColumnPkIndex = configSh.getNumberValueFromSectionKVTable(SECTION_CONFIG_TITLE, CONFIG_KEY__IS_COLUMN_PK_INDEX, defaultVal:=CONFIG_VAL__IS_COLUMN_PK_INDEX)
End Property

' 获取 config 下的属性: tagColumn
Public Property Get Config_tagColumn() As String
    Config_tagColumn = configSh.getValueFromSectionKVTable(SECTION_CONFIG_TITLE, CONFIG_KEY__TAG_COLUMN, defaultVal:=CONFIG_VAL__TAG_COLUMN)
End Property

' 获取 config 下的属性: tableDefineStartColumn
Public Property Get Config_tableDefineStartColumn() As String
    Config_tableDefineStartColumn = configSh.getValueFromSectionKVTable(SECTION_CONFIG_TITLE, CONFIG_KEY__TABLE_DEFINE_START_COLUMN, defaultVal:=CONFIG_VAL__TABLE_DEFINE_START_COLUMN)
End Property

' 获取 config 下的属性: sqlParamTagLeft
Public Property Get Config_sqlParamTagLeft() As String
    Config_sqlParamTagLeft = configSh.getValueFromSectionKVTable(SECTION_CONFIG_TITLE, CONFIG_KEY__SQL_PARAM_TAG_LEFT, defaultVal:=CONFIG_VAL__SQL_PARAM_TAG_LEFT)
End Property

' 获取 config 下的属性: sqlParamTagRight
Public Property Get Config_sqlParamTagRight() As String
    Config_sqlParamTagRight = configSh.getValueFromSectionKVTable(SECTION_CONFIG_TITLE, CONFIG_KEY__SQL_PARAM_TAG_RIGHT, defaultVal:=CONFIG_VAL__SQL_PARAM_TAG_RIGHT)
End Property

' ==================================================
' Property--dataStrategy
' ==================================================
' 获取 dataStrategy 部分的起始行号
Public Property Get SectionDataStrategyStartRowNo() As Long
    SectionDataStrategyStartRowNo = configSh.getSectionUsageStartRowNo(SECTION_DATA_STRATEGY_TITLE)
End Property

' 获取 dataStrategy 部分的结束行号
Public Property Get SectionDataStrategyEndRowNo() As Long
    SectionDataStrategyEndRowNo = configSh.getSectionUsageLastRowNo(SECTION_DATA_STRATEGY_TITLE)
End Property

' ==================================================
' Property--SQL
' ==================================================
' 获取 SQL 部分使用的区域的起始行号和结束行号
Public Property Get SectionSQLRangeRowNo() As PrimitivePair
    Set SectionSQLRangeRowNo = configSh.getSectionUsageRangeRowNo(SECTION_SQL_TITLE)
End Property

' 获取 SQL 部分的起始行号
Public Property Get SectionSQLStartRowNo() As Long
    SectionSQLStartRowNo = configSh.getSectionUsageStartRowNo(SECTION_SQL_TITLE)
End Property

' 获取 SQL 部分的结束行号
Public Property Get SectionSQLEndRowNo() As Long
    SectionSQLEndRowNo = configSh.getSectionUsageLastRowNo(SECTION_SQL_TITLE)
End Property

' ==================================================
' Property--SQLParameter
' ==================================================
' 获取 SQLParameter 部分使用的区域的起始行号和结束行号
Public Property Get SectionSQLParameterRangeRowNo() As PrimitivePair
    Set SectionSQLParameterRangeRowNo = configSh.getSectionUsageRangeRowNo(SECTION_SQL_PARAMETER_TITLE)
End Property

' 获取 SQLParameter 部分的起始行号
Public Property Get SectionSQLParameterStartRowNo() As Long
    SectionSQLParameterStartRowNo = configSh.getSectionUsageStartRowNo(SECTION_SQL_PARAMETER_TITLE)
End Property

' 获取 SQLParameter 部分的结束行号
Public Property Get SectionSQLParameterEndRowNo() As Long
    SectionSQLParameterEndRowNo = configSh.getSectionUsageLastRowNo(SECTION_SQL_PARAMETER_TITLE)
End Property

' 获取 SQLParameter 下所有的参数的 Map
' @return Map<String, SQLParamDescriptor>
Public Property Get SQLParameterMap() As Object
    Dim triFunc As SqlConfigSheetSqlParamDescFunc
    Set triFunc = New SqlConfigSheetSqlParamDescFunc

    Set SQLParameterMap = configSh.getMapFromSectionKeyMultiValTable(SECTION_SQL_PARAMETER_TITLE, triFunc)
End Property

' 获取 SQLParameter 下所有的参数的 Key
Public Property Get SQLParameterKeys() As Collection
    Set SQLParameterKeys = configSh.getAllKeysFromSectionKVTable(SECTION_SQL_PARAMETER_TITLE)
End Property

' 获取 SQLParameter 下所有的参数对应的 value
Public Property Get SQLParameterValues() As Collection
    Set SQLParameterValues = configSh.getAllValuesFromSectionKVTable(SECTION_SQL_PARAMETER_TITLE)
End Property

' ==================================================
' Property--tableDefine
' ==================================================
' 获取 tableDefine 部分使用的区域的起始行号和结束行号
Public Property Get SectionTableDefineRangeRowNo() As PrimitivePair
    Set SectionTableDefineRangeRowNo = configSh.getSectionUsageRangeRowNo(SECTION_TABLE_DEFINE_TITLE)
End Property

' 获取 tableDefine 部分的起始行号
Public Property Get SectionTableDefineStartRowNo() As Long
    SectionTableDefineStartRowNo = configSh.getSectionUsageStartRowNo(SECTION_TABLE_DEFINE_TITLE)
End Property

' 获取 tableDefine 部分的结束行号
Public Property Get SectionTableDefineEndRowNo() As Long
    SectionTableDefineEndRowNo = configSh.getSectionUsageLastRowNo(SECTION_TABLE_DEFINE_TITLE)
End Property

' ======================================================================
' ======================================================================
' 对外可以使用的功能
' ======================================================================
' ======================================================================

' 获取 SQL 下写入的 SQL
Public Function getSQL() As String
    getSQL = configSh.getSectionContentInCol(SECTION_SQL_TITLE)
End Function

' 获取指定 key 对应的 SQLParameter 的 value
' 如果指定 key 不存在，则返回空字符串
Public Function getSQLParameterValue(ByRef key As String) As String
    getSQLParameterValue = configSh.getValueFromSectionKVTable(SECTION_SQL_PARAMETER_TITLE, key)
End Function

' ======================================================================
' 从 SQL 中抽取参数
' ======================================================================
Public Sub extractSQLParameter()
    ' ==================================================
    ' 1. 构造需要写入 sheet 的数据表格
    ' ==================================================
    ' 1. 获取 sql 中的参数
    Dim sqlParameterSet As ISet
    Set sqlParameterSet = getSQLParameterSet

    ' 2. 构造表格
    Dim df As DataFrame
    Set df = New DataFrame
    df.Init colNames:= Array( _
        SQL_PARAMETER_TABLE_TITLE_KEY, _
        SQL_PARAMETER_TABLE_TITLE_VAL, _
        SQL_PARAMETER_TABLE_TITLE_TYPE _
    )

    ' 3. 将 sql 参数写入第一列
    Dim i As Long
    With df
        For i = 0 To sqlParameterSet.Count - 1
            .IDataFrame_appendRowByColDatas sqlParameterSet.Item(i)
        Next
    End With

    ' ==================================================
    ' 2. 将抽取到的 SQL 参数写入 [SQLParameter] 内，上一次的参数需要保留并移动到新参数的右侧
    ' ==================================================
    configSh.writeDataFrameInSection _
        SECTION_SQL_PARAMETER_TITLE, _
        df, _
        titleBgColorNo:=UtilsContentFormat.COLOR_GREEN, _
        lastSectionName:=SECTION_SQL_TITLE, _
        needReserveOldData:=True
End Sub

' 获取 sql 中的 mybatis 参数名，并保存到一个 Set
Public Function getSQLParameterSet() As ISet
    ' ==================================================
    ' 1. 获取 SQL
    ' ==================================================
    Dim sqlStr as String
    sqlStr = getSQL

    ' ==================================================
    ' 2. 创建结果对象
    ' ==================================================
    Set getSQLParameterSet = UtilsSet.createLinkedHashSet

    ' ==================================================
    ' 3. 获取 SQL 中的参数
    ' ==================================================
    Dim sqlLen As Long
    sqlLen = Len(sqlStr)

    Dim sqlParamTagLeft As String
    Dim sqlParamTagRight As String
    sqlParamTagLeft = Config_sqlParamTagLeft
    sqlParamTagRight = Config_sqlParamTagRight

    Dim sqlParamTagLeftLen As Integer
    Dim sqlParamTagRightLen As Integer
    sqlParamTagLeftLen = Len(sqlParamTagLeft)
    sqlParamTagRightLen = Len(sqlParamTagRight)

    Dim sqlParamTagLeftIndex As Integer
    Dim sqlParamTagRightIndex As Integer
    sqlParamTagRightIndex = 1

    Dim scannedSqlStrIdx As Integer
    scannedSqlStrIdx = 1
    Dim sqlStrLen as Integer
    sqlStrLen = Len(sqlStr)

    While scannedSqlStrIdx < sqlStrLen
        ' 1. 搜索左标签的位置
        sqlParamTagLeftIndex = InStr(scannedSqlStrIdx, sqlStr, sqlParamTagLeft)

        If sqlParamTagLeftIndex <> 0 Then
            ' 2. 从左标签开始，搜索右标签的位置
            sqlParamTagRightIndex = InStr(sqlParamTagLeftIndex, sqlStr, sqlParamTagRight)
            If sqlParamTagRightIndex = 0 Then
                ' 如果有左标签，但是没有右标签，则说明 SQL 书写异常
                Err.Raise 1500, Description:="SQL syntax error. From index of last left tag: " & sqlParamTagLeftIndex & ", can not find right tag: " + sqlParamTagRight
            End If

            ' 3. 截取参数，并搜索参数数据
            getSQLParameterSet.Add Mid(sqlStr, sqlParamTagLeftIndex + sqlParamTagLeftLen, sqlParamTagRightIndex - sqlParamTagLeftIndex - sqlParamTagLeftLen)

            ' 4. 将索引位置向右推进
            scannedSqlStrIdx = sqlParamTagRightIndex + sqlParamTagRightLen
        Else
            ' 到这里说明已经没有左标签了，即没有参数了，并将索引位置推进到末尾
            scannedSqlStrIdx = sqlStrLen + 1
        End If
    Wend
End Function

' ======================================================================
' 填充 SQL数据生成配置 sheet 中的 SQL 内的参数
' ======================================================================
Public Sub fillSQLParameter()
    ' ==================================================
    ' 1. 获取 sql
    ' ==================================================
    ' 1. 获取 sql
    Dim sqlStr as String
    sqlStr = getSQL

    ' 2. 检查: 如果 sql 是空字符串，则退出
    If sqlStr = "" Then
        MsgBox "not find sql"
        Exit Sub
    End If

    ' ==================================================
    ' 2. 将 sql 参数值填充到 sql
    ' ==================================================
    ' 1. 创建缓存，用来保存没有匹配到的参数
    Dim noFillKeySet As ISet
    Set noFillKeySet = UtilsSet.createLinkedHashSet

    ' 2. 填充参数
    Dim filledSql As String
    filledSql = doFillSQLParameter(sqlStr, noFillKeySet)

    ' ==================================================
    ' 4. 将填充后的 sql 写入新的 sheet
    ' ==================================================
    ' 1. 从 configSheet 所在的 Workbook 中，获取指定名称的 sheet 对象
    Dim targetSheetName As String
    Dim targetSheet As Worksheet
    With configSh
        targetSheetName = .Name & "_fillSql"
        Set targetSheet = UtilsWorkbook.tryGetEmptyTextSheet(.Book, targetSheetName)
    End With

    ' 2. 保存填充后的 sql
    UtilsWorksheet.pasteStrToSheet targetSheet, filledSql, "B2"

    ' 3. 记录当前生成/更新的 sheet 名
    writeLastCreatedOrUpdatedSheetName targetSheetName

    ' ==================================================
    ' 5. 激活保存了 sql 的sheet
    ' ==================================================
    targetSheet.Activate

    ' ==================================================
    ' 6. 如果存在未找到的参数，则抛出异常
    ' ==================================================
    If noFillKeySet.count <> 0 Then
        MsgBox "can not find these paramKey :" & chr(10) & noFillKeySet.Join(", ")
    End If
End Sub

' 向指定 sql 中填充参数数据
' @param sql 需要填充参数数据的sql
' @param noFillKeySet 用来缓存没有被填充的参数名
'                     必须有调用者提供，否则会发生异常
' @return String 填充参数数据后的 sql
Private Function doFillSQLParameter(ByRef sqlStr As String, ByRef noFillKeySet As ISet) As String
    ' 1. 获取 SQLParameter 下所有的参数的 Map
    Dim paramMap As Object
    Set paramMap = SQLParameterMap

    ' 2. 填充 SQL 中的参数
    Dim result As String
    result = ""

    Dim sqlParamTagLeft As String
    Dim sqlParamTagRight As String
    sqlParamTagLeft = Config_sqlParamTagLeft
    sqlParamTagRight = Config_sqlParamTagRight

    Dim sqlParamTagLeftLen As Integer
    Dim sqlParamTagRightLen As Integer
    sqlParamTagLeftLen = Len(sqlParamTagLeft)
    sqlParamTagRightLen = Len(sqlParamTagRight)

    Dim sqlParamTagLeftIndex As Integer
    Dim sqlParamTagRightIndex As Integer

    ' 3. 获取参数部分的左标签位置
    Dim paramDiscriptor As SQLParamDescriptor
    Dim paramKey As String
    Dim scannedSqlStrIdx As Integer
    scannedSqlStrIdx = 1

    Dim sqlStrLen as Integer
    sqlStrLen = Len(sqlStr)
    While scannedSqlStrIdx < sqlStrLen
        ' 3.1 搜索左标签的位置
        sqlParamTagLeftIndex = InStr(scannedSqlStrIdx, sqlStr, sqlParamTagLeft)

        If sqlParamTagLeftIndex <> 0 Then
            ' 3.2 暂存无关的 sql 内容
            result = result & Mid(sqlStr, scannedSqlStrIdx, sqlParamTagLeftIndex - scannedSqlStrIdx)

            ' 3.3 从左标签开始，搜索右标签的位置
            sqlParamTagRightIndex = InStr(sqlParamTagLeftIndex, sqlStr, sqlParamTagRight)
            If sqlParamTagRightIndex = 0 Then
                ' 如果有左标签，但是没有右标签，则说明 SQL 书写异常
                Err.Raise 1500, Description:="SQL syntax error. From index of last left tag: " & sqlParamTagLeftIndex & ", can not find right tag: " + sqlParamTagRight
            End If

            ' 3.4 截取参数，并搜索参数数据
            paramKey = Mid(sqlStr, sqlParamTagLeftIndex + sqlParamTagLeftLen, sqlParamTagRightIndex - sqlParamTagLeftIndex - sqlParamTagLeftLen)
            If paramMap.Exists(paramKey) Then
                Set paramDiscriptor = paramMap(paramKey)

                ' 按照不同的类型进行参数值的连接
                If paramDiscriptor.ParamType = UtilsDBTable.TYPE_INTEGER Or paramDiscriptor.ParamType = UtilsDBTable.TYPE_DECIMAL Then
                    ' 数值类型: 直接连接
                    result = result & paramDiscriptor.ParamValue
                Else
                    ' 其他类型，全部当前字符串处理，在两侧附加单引号
                    result = result & "'" & paramDiscriptor.ParamValue & "'"
                End If
            Else
                ' 如果没有找到对应的参数 key，则缓存这个 key，在结束时作为异常消息
                noFillKeySet.Add paramKey

                ' 将原始参数写入填充后的 sql 中
                result = result & sqlParamTagLeft _
                    & paramKey _
                    & sqlParamTagRight
            End If

            ' 3.5 将索引位置向右推进
            scannedSqlStrIdx = sqlParamTagRightIndex + sqlParamTagRightLen
        Else
            ' 到这里说明已经没有左标签了，即没有参数了，并将索引位置推进到末尾
            result = result & Mid(sqlStr, scannedSqlStrIdx)
            scannedSqlStrIdx = sqlStrLen + 1
        End If
    Wend

    ' 4. 替换特殊字符
    ' &lt; --> <
    result = Replace(result, "&lt;", "<")
    ' &gt; --> >
    result = Replace(result, "&gt;", ">")

    doFillSQLParameter = result
End Function

' ======================================================================
' 拷贝 SQL 区内的 到剪贴板
' ======================================================================
Public Sub copySQLToClipBoard()
    Dim sqlStr As String
    sqlStr = Me.getSQL
    UtilsIO.copyStr2Clipboard sqlStr
End Sub

' ======================================================================
' 读取 SQL 内容单元中的 sql 后，将 sql 的形式转换成标准化的格式，并写回 SQL 内容单元
' ======================================================================
Public Sub formatSql()
    ' 1. 获取原始 sql
    Dim sqlStr as String
    sqlStr = getSQL
    '    如果 sql 是空，则退出
    If sqlStr = "" Then
        Exit Sub
    End If

    ' 2. 生成 sql 格式化器，并进行格式化
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config
    formatter.analyze sqlStr

    ' 3. 清空旧的 sql，然后写入新的sql
    configSh.overwriteStrInSection _
        SECTION_SQL_TITLE, formatter.getSql, _
        lineCount:=formatter.getSqlLineSize, minLineCount:=9
End Sub

' ======================================================================
' 获取 tableDefine 内容单元中的所有表定义
' ======================================================================
' @return Map<String, DBTableDefine>
Public Function getTableDefineMap() As Object
    Set getTableDefineMap = UtilsCreateSysObj.createMap
    ' Set getTableDefineMap = New SimpleMap

    ' 获取 SQLParameter 的使用范围
    Dim rangeRowNo As PrimitivePair
    Set rangeRowNo = SectionTableDefineRangeRowNo

    Dim startRowNo As Long
    Dim endRowNo As Long
    startRowNo = rangeRowNo.Key1
    endRowNo = rangeRowNo.Key2
    ' 检查: 如果 SQL 不存在，则退出
    If startRowNo = -1 Then
        Exit Function
    End If

    Dim tagColumn As String
    tagColumn = Config_tagColumn

    ' 2. 获取第一个表定义的起始单元格
    Dim realSheet As Worksheet
    Set realSheet = configSh.Sheet

    Dim startRange As Range
    Set startRange = realSheet.Range(tagColumn & (startRowNo + 1))
    If startRange.Value = "" Then
        Set startRange = startRange.End(xlDown)
        If startRange.Row > endRowNo Then
            Exit Function
        End If
    End If

    Dim tableDefineStartColumn As String
    Dim tableIdIndex As Long
    Dim logicNameIndex As Long
    Dim physicalNameIndex As Long
    Dim columnTypeIndex As Long
    Dim columnSizeIndex As Long
    Dim isColumnPkIndex As Long
    Dim pkMark As String

    ' 3. 获取配置中的各个索引位置
    tableDefineStartColumn = Config_tableDefineStartColumn
    tableIdIndex = Config_tableIdIndex
    logicNameIndex = Config_logicNameIndex
    physicalNameIndex = Config_physicalNameIndex
    columnTypeIndex = Config_columnTypeIndex
    columnSizeIndex = Config_columnSizeIndex
    isColumnPkIndex = Config_isColumnPkIndex
    pkMark = Config_pkMark

    Dim defineRangeEndRowNo As Long
    Dim defineRangeStartRowNo As Long
    Dim i As Long
    Dim maxColumnNo As Integer
    Dim tempColumnNo As Integer
    Dim tableDefine As DBTableDefine
    With realSheet

        ' 4. 遍历整个 [tableDefine] 区域，搜索并创建所有的表定义
        '    结束条件: 起始单元格的行号已经超出了 [tableDefine] 区域的结束行号
        While startRange.Row <= endRowNo
            ' 4.1 搜索一个表定义的有效区域
            ' 4.1.1 获取表定义的起始行号
            defineRangeStartRowNo = startRange.Row
            ' 4.1.2 获取表定义的结束行号
            defineRangeEndRowNo = startRange.End(xlDown).Row

            ' 4.1.3 获取列定义的结束列
            ' 从表定义的起始行遍历到结束行，搜索所有行中最大的使用范围
            maxColumnNo = 0
            For i = defineRangeStartRowNo To defineRangeEndRowNo
                tempColumnNo = .Range(tagColumn & i).End(xlToRight).Column

                If maxColumnNo < tempColumnNo Then
                    maxColumnNo = tempColumnNo
                End If
            Next

            ' 4.2 使用表定义的区域创建表定义
            Set tableDefine = New DBTableDefine
            tableDefine.Init _
                realSheet, _
                defineRangeStartRowNo, _
                startRange.Column, _
                defineRangeEndRowNo, _
                maxColumnNo, _
                tableDefineStartColumn, _
                tableIdIndex, _
                logicNameIndex, _
                physicalNameIndex, _
                columnTypeIndex, _
                columnSizeIndex, _
                isColumnPkIndex, _
                pkMark

            ' 4.3 缓存表定义
            getTableDefineMap.Add tableDefine.TableId, tableDefine

            ' 4.4 计算下一个表定义的起始单元格
            Set startRange = .Range(tagColumn & defineRangeEndRowNo).End(xlDown)
        Wend
    End With
End Function

' ======================================================================
' Common
' ======================================================================
' 记录由当前 sheet 的操作而新生成的，或者发生更新的 sheet 名
'
' 对于生成 sql、生成数据的操作，可能会生成一个或多个新的 sheet
' 每次结束后可以通过该方法将生成的 sheet 名写入配置 sheet 来进行记录
Private Sub writeLastCreatedOrUpdatedSheetName(ByRef sheetNameStr As String)
    configSh.writeKVInSectionKVTable SECTION_CONFIG_TITLE, CONFIG_KEY__SQL_PARAM_LAST_CREATED_UPDATED_SHEET_NAME, sheetNameStr
End Sub
